package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;
import com.groot.medical.entity.ChronicExpense;
import com.groot.medical.entity.Pay;

public class ChronicExamineDAOImpl implements ChronicExamineDAO {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public int getCounts(String name, String starTime,String endTime,String areacode,String ispass, String isremittance) {
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(ce.id) id FROM t_personnel_file pf,t_chronic_expense ce,t_chronictype tc WHERE 1=1 AND ce.ill_code=tc.ill_code AND ce.idcard_numeber=pf.idcard_number ");
		// TODO Auto-generated method stub
		 if(starTime != null && !starTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
	            sql.append(" AND ce.expense_time between '"+starTime+"' and '"+endTime+"'");
	        }if(name != null && !name.trim().equals("")){
	        	sql.append(" AND pf.`name` LIKE '%"+name+"%'");
	        }if(areacode != null && !areacode.trim().equals("")){
	        	sql.append(" AND ce.farmingcard_code LIKE '%"+areacode+"%'");
	        }if(ispass != null && !ispass.trim().equals("")){
	        	sql.append(" AND ce.is_pass='"+ispass+"'");
	        }if(isremittance != null && !isremittance.trim().equals("")){
	        	sql.append(" AND ce.is_remittance='"+isremittance+"'");
	        }
	        //查询结果
	        try {
	        	 conn = MedicalDBUtil.getConnection();
	             ps = conn.prepareStatement(sql.toString());
	             rs = ps.executeQuery();
	            if(rs.next()) {
	                count = rs.getInt("id");
	            }
	        } catch (SQLException e) {

	    		e.printStackTrace();
	    		}
	    		finally{
	    			MedicalDBUtil.closeAll(conn, ps, rs);
	    		}
	        return count;
	}
	@Override
	public List<ChronicExpense> findChronicExamineByPage(int startIndex, int pageSize, String name, String startTime,
			String endTime, String areacode,String ispass, String isremittance) {
		StringBuffer sql = new StringBuffer("SELECT ce.id,pf.`name`,ce.idcard_numeber,ce.farmingcard_code,tc.ill_name,ce.expense_money,ce.current_year,ce.expense_time,ce.is_pass,ce.is_remittance FROM t_personnel_file pf,t_chronic_expense ce,t_chronictype tc WHERE 1=1 AND ce.ill_code=tc.ill_code AND ce.idcard_numeber=pf.idcard_number ");
		// TODO Auto-generated method stub
		 if(startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
	            sql.append(" AND ce.expense_time between '"+startTime+"' and '"+endTime+"'");
	        }if(name != null && !name.trim().equals("")){
	        	sql.append(" AND pf.`name` LIKE '%"+name+"%'");
	        }if(areacode != null && !areacode.trim().equals("")){
	        	sql.append(" AND ce.farmingcard_code LIKE '%"+areacode+"%'");
	        }if(ispass != null && !ispass.trim().equals("")){
	        	sql.append(" AND ce.is_pass='"+ispass+"'");
	        }if(isremittance != null && !isremittance.trim().equals("")){
	        	sql.append(" AND ce.is_remittance='"+isremittance+"'");
	        }
	        sql.append(" limit ?,? " );
	        System.out.println(sql.toString());
	        try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				ps.setInt(1, startIndex);
				ps.setInt(2, pageSize);
				rs = ps.executeQuery();
				List<ChronicExpense> expenses = new ArrayList<>();
				
				while(rs.next()){
					/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
					ChronicExpense expense = new ChronicExpense();
					expense.setId(rs.getInt(1));
					expense.setHospitalTicket(rs.getString(2));
					expense.setIdCardNumber(rs.getString(3));
					expense.setFarmingCardCode(rs.getString(4));
					expense.setIllCode(rs.getString(5));
					expense.setExpenseMoney(rs.getDouble(6));
					expense.setCurrentYear(rs.getString(7));
					expense.setExpenseTime(rs.getTimestamp(8));
					expense.setIsPass(rs.getInt(9));
					expense.setIsRemittance(rs.getInt(10));
					expenses.add(expense);
					
				}
				
				return expenses;
				
			} catch (SQLException e) {
				e.printStackTrace();
				
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return null;

}
	@Override
	public int getCountsforStatistics(String name, String startTime, String endTime, String areacode, String illCode) {
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(ce.id) id FROM t_personnel_file pf,t_chronic_expense ce,t_chronictype tc WHERE 1=1 AND ce.ill_code=tc.ill_code AND ce.idcard_numeber=pf.idcard_number ");
		// TODO Auto-generated method stub
		 if(startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
	            sql.append(" AND ce.expense_time between '"+startTime+"' and '"+endTime+"'");
	        }if(name != null && !name.trim().equals("")){
	        	sql.append(" AND pf.`name` LIKE '%"+name+"%'");
	        }if(areacode != null && !areacode.trim().equals("")){
	        	sql.append(" AND ce.farmingcard_code LIKE '%"+areacode+"%'");
	        }if(illCode != null && !illCode.trim().equals("")){
	        	sql.append(" AND ce.ill_code='"+illCode+"'");
	        }
	        //查询结果
	        try {
	        	 conn = MedicalDBUtil.getConnection();
	             ps = conn.prepareStatement(sql.toString());
	             rs = ps.executeQuery();
	            if(rs.next()) {
	                count = rs.getInt("id");
	            }
	        } catch (SQLException e) {

	    		e.printStackTrace();
	    		}
	    		finally{
	    			MedicalDBUtil.closeAll(conn, ps, rs);
	    		}
	        return count;
	}
	@Override
	public List<ChronicExpense> statisticsChronicExpense(int startIndex, int pageSize, String name, String startTime,
			String endTime, String areacode, String illCode) {
		StringBuffer sql = new StringBuffer("SELECT ce.id,pf.`name`,ce.idcard_numeber,ce.farmingcard_code,tc.ill_name,ce.expense_money,ce.current_year,ce.expense_time,ce.is_pass,ce.is_remittance FROM t_personnel_file pf,t_chronic_expense ce,t_chronictype tc WHERE 1=1 AND ce.ill_code=tc.ill_code AND ce.idcard_numeber=pf.idcard_number ");
		// TODO Auto-generated method stub
		 if(startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
	            sql.append(" AND ce.expense_time between '"+startTime+"' and '"+endTime+"'");
	        }if(name != null && !name.trim().equals("")){
	        	sql.append(" AND pf.`name` LIKE '%"+name+"%'");
	        }if(areacode != null && !areacode.trim().equals("")){
	        	sql.append(" AND ce.farmingcard_code LIKE '%"+areacode+"%'");
	        }if(illCode != null && !illCode.trim().equals("")){
	        	sql.append(" AND ce.ill_code='"+illCode+"'");
	        }
	        sql.append(" limit ?,? " );
	        System.out.println(sql.toString());
	        try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				ps.setInt(1, startIndex);
				ps.setInt(2, pageSize);
				rs = ps.executeQuery();
				List<ChronicExpense> expenses = new ArrayList<>();
				
				while(rs.next()){
					/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
					ChronicExpense expense = new ChronicExpense();
					expense.setId(rs.getInt(1));
					expense.setHospitalTicket(rs.getString(2));
					expense.setIdCardNumber(rs.getString(3));
					expense.setFarmingCardCode(rs.getString(4));
					expense.setIllCode(rs.getString(5));
					expense.setExpenseMoney(rs.getDouble(6));
					expense.setCurrentYear(rs.getString(7));
					expense.setExpenseTime(rs.getTimestamp(8));
					expense.setIsPass(rs.getInt(9));
					expense.setIsRemittance(rs.getInt(10));
					expenses.add(expense);
					
				}
				
				return expenses;
				
			} catch (SQLException e) {
				e.printStackTrace();
				
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return null;
	}
	@Override
	public List<ChronicExpense> findByCondition(String name, String stratTime, String endTime, String areacode,String illCode) {
		StringBuffer sql = new StringBuffer("SELECT ce.id,pf.`name`,ce.idcard_numeber,ce.farmingcard_code,tc.ill_name,ce.expense_money,ce.current_year,ce.expense_time,ce.is_pass,ce.is_remittance FROM t_personnel_file pf,t_chronic_expense ce,t_chronictype tc WHERE 1=1 AND ce.ill_code=tc.ill_code AND ce.idcard_numeber=pf.idcard_number ");
		// TODO Auto-generated method stub
		 if(stratTime != null && !stratTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
	            sql.append(" AND ce.expense_time between '"+stratTime+"' and '"+endTime+"'");
	        }if(name != null && !name.trim().equals("")){
	        	sql.append(" AND pf.`name` LIKE '%"+name+"%'");
	        }if(areacode != null && !areacode.trim().equals("")){
	        	sql.append(" AND ce.farmingcard_code LIKE '%"+areacode+"%'");
	        }if(illCode != null && !illCode.trim().equals("")){
	        	sql.append(" AND ce.ill_code='"+illCode+"'");
	        }
	      
	        System.out.println(sql.toString());
	        try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				
				rs = ps.executeQuery();
				List<ChronicExpense> expenses = new ArrayList<>();
				
				while(rs.next()){
					/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
					ChronicExpense expense = new ChronicExpense();
					expense.setId(rs.getInt(1));
					expense.setHospitalTicket(rs.getString(2));
					expense.setIdCardNumber(rs.getString(3));
					expense.setFarmingCardCode(rs.getString(4));
					expense.setIllCode(rs.getString(5));
					expense.setExpenseMoney(rs.getDouble(6));
					expense.setCurrentYear(rs.getString(7));
					expense.setExpenseTime(rs.getTimestamp(8));
					expense.setIsPass(rs.getInt(9));
					expense.setIsRemittance(rs.getInt(10));
					expenses.add(expense);
					
				}
				
				return expenses;
				
			} catch (SQLException e) {
				e.printStackTrace();
				
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return null;
	}
}
